﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Globalization;
using System.Data.SqlClient;

namespace InventoryManager
{
    public partial class AddRequisitionForm_1_UserControl : UserControl
    {
        public event FormChangedHandler FormChangedEvent;

        private string _connectionString;
        private MySqlConnection _connection;
        private BindingSource _dataGridViewBindingSource;
        private DataTable _dataGridViewDataTable;
        private MySqlDataAdapter _mySqlDataAdapter;

        private const string QUANTITY = "จำนวน";
        private const string CLASSIFIER = "หน่วย";
        private const string WEIGHT = "น้ำหนัก (kg)";
        private const string PRODUCTNAME = "ชื่อสินค้า";
        private const string DIMENSION = "ขนาด";
        private const string UNITPRICE = "ราคาหน่วยละ";
        private const string UNITNAME = "หน่วยที่ใช้";
        private const string AMOUNT = "จำนวนเงิน";

        public AddRequisitionForm_1_UserControl(string connectionString, int width, int height)
        {
            InitializeComponent();

            _connectionString = connectionString;
            this.Width = width;
            this.Height = height;

            IncludeVatUpDown.Value = Utility.GetDefaultVat();

            LoadCustomerName();
        }

        private void LoadCustomerName()
        {
            try
            {
                _connection = new MySqlConnection(_connectionString);
                _connection.Open();

                string query = "SELECT Name, CustomerID FROM customer ORDER BY CustomerID ASC";

                _mySqlDataAdapter = new MySqlDataAdapter(query, _connection);

                DataTable comboBoxDataTable = new DataTable();
                _mySqlDataAdapter.Fill(comboBoxDataTable);

                _dataGridViewDataTable = new DataTable();
                _dataGridViewDataTable.Columns.Add(QUANTITY);
                _dataGridViewDataTable.Columns.Add(CLASSIFIER);
                _dataGridViewDataTable.Columns.Add(WEIGHT);
                _dataGridViewDataTable.Columns.Add(PRODUCTNAME);
                _dataGridViewDataTable.Columns.Add(DIMENSION);
                _dataGridViewDataTable.Columns.Add(UNITPRICE);
                _dataGridViewDataTable.Columns.Add(UNITNAME);
                _dataGridViewDataTable.Columns.Add(AMOUNT);
                _dataGridViewBindingSource = new BindingSource();
                _dataGridViewBindingSource.DataSource = _dataGridViewDataTable;
                AddRequisitionFormDataGridView.DataSource = _dataGridViewBindingSource;

                AddRequisitionFormBindingNavigator.BindingSource = _dataGridViewBindingSource;

                BindingSource comboBoxbindingSource = new BindingSource();
                comboBoxbindingSource.DataSource = comboBoxDataTable;

                foreach (DataRow row in comboBoxDataTable.Rows)
                    CustomerNameComboBox.Items.Add(row.ItemArray[0].ToString());

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                _connection.Close();
            }
        }

        private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
        {
            ProductSelectionForm productForm = new ProductSelectionForm(_connectionString, false);

            switch (productForm.ShowDialog())
            {
                case DialogResult.OK:
                    DataRow dtRow = _dataGridViewDataTable.NewRow();

                    dtRow[QUANTITY] = productForm.Quantity;
                    dtRow[CLASSIFIER] = productForm.Classifier;
                    dtRow[WEIGHT] = productForm.Weight;
                    dtRow[PRODUCTNAME] = productForm.ProdName;
                    dtRow[DIMENSION] = productForm.Dimension;
                    dtRow[UNITPRICE] = productForm.PricePerUnit;
                    dtRow[UNITNAME] = productForm.UnitName;

                    if (productForm.UnitSelected)
                    {
                        double pricePerItem;
                        int quantity = Int32.Parse(productForm.Quantity);
                        double amount;

                        pricePerItem = Double.Parse(dtRow[UNITPRICE].ToString());
                        amount = pricePerItem * quantity;
                        dtRow[AMOUNT] = amount.ToString("N2", CultureInfo.InvariantCulture);
                    }
                    else
                    {
                        double pricePerWeight;
                        double weight = Double.Parse(productForm.Weight);
                        double amount;

                        pricePerWeight = Double.Parse(dtRow[UNITPRICE].ToString());
                        amount = pricePerWeight * weight;
                        dtRow[AMOUNT] = amount.ToString("N2", CultureInfo.InvariantCulture);
                    }

                    _dataGridViewDataTable.Rows.Add(dtRow);

                    CalculateTotal();

                    break;
                case DialogResult.Cancel:
                    break;
                default:
                    break;
            }
        }

        private void CalculateTotal()
        {
            if (_dataGridViewDataTable == null)
            {
                return;
            }

            double total = 0;

            foreach (DataRow dtRow in _dataGridViewDataTable.Rows)
            {
                total += Double.Parse(dtRow[AMOUNT].ToString());
            }

            if (!IncludeVatCheckBox.Checked)
            {
                total *= 1 + (double)(IncludeVatUpDown.Value / 100);
            }
            
            TotalLabelValue.Text = total.ToString("N2", CultureInfo.InvariantCulture);
            TotalLabelValue.Text = total.ToString("N2", CultureInfo.InvariantCulture);
            AddRequisitionFormButton.Visible = (_dataGridViewDataTable.Rows.Count != 0 && CustomerNameComboBox.SelectedIndex != -1);
            TotalLabelName.Visible = (_dataGridViewDataTable.Rows.Count != 0);
            TotalLabelValue.Visible = (_dataGridViewDataTable.Rows.Count != 0);
            BahtLabel.Visible = (_dataGridViewDataTable.Rows.Count != 0);
        }

        private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {
            CalculateTotal();
        }

        private void AddRequisitionFormButton_Click(object sender, EventArgs e)
        {
            int lastRequisitionID = -1;

            try
            {
                _connection.Open();

                // insert a new row to RequisitionIDCustomerName table
                string customerName = CustomerNameComboBox.Items[CustomerNameComboBox.SelectedIndex].ToString();
                MySqlCommand cmd = _connection.CreateCommand();

                string query = "SELECT CustomerID FROM customer WHERE Name = \'" + customerName + "\'";
                _mySqlDataAdapter = new MySqlDataAdapter(query, _connection);

                DataTable customerIDDataTable = new DataTable();
                _mySqlDataAdapter.Fill(customerIDDataTable);
                int customerID = Int32.Parse(customerIDDataTable.Rows[0][0].ToString());

                cmd.Parameters.AddWithValue("Date", DateTime.Now);

                if(DeliveryNumericUpDown.Visible)
                    cmd.Parameters.AddWithValue("DeliveryPrice", DeliveryNumericUpDown.Value);
                else
                    cmd.Parameters.AddWithValue("DeliveryPrice", 0);

                if (IncludeVatCheckBox.Checked)
                    cmd.Parameters.AddWithValue("VatIncluded", true);
                else
                    cmd.Parameters.AddWithValue("VatIncluded", false);

                cmd.Parameters.AddWithValue("VatIncludedValue", IncludeVatUpDown.Value);

                cmd.Parameters.AddWithValue("IsInvoice", 0);
                cmd.CommandText = "INSERT INTO requisitionidcustomername (RequisitionID, CustomerID, Date, DeliveryPrice, VatIncluded, VatIncludedValue, IsInvoice) " +
                    "VALUES " + "(0, \'" + customerID + "\', ?Date, ?DeliveryPrice, ?VatIncluded, ?VatIncludedValue, ?IsInvoice)";
                cmd.ExecuteNonQuery();

                // insert new rows to RequisitionIDDescription table and update rows in ItemsRemin column in Product table
                MySqlCommand insertCmd = _connection.CreateCommand();
                MySqlCommand updateCmd = _connection.CreateCommand();
                MySqlCommand getRequisitionIDCmd = _connection.CreateCommand();
                
                MySqlParameter parRequisitionID = new MySqlParameter("RequisitionID", MySqlDbType.Int32);
                MySqlParameter parQuantity = new MySqlParameter("Quantity", MySqlDbType.Int32);
                MySqlParameter parClassifier = new MySqlParameter("Classifier", MySqlDbType.VarChar);
                MySqlParameter parWeight = new MySqlParameter("Weight", MySqlDbType.Float);
                MySqlParameter parProductName = new MySqlParameter("ProductName", MySqlDbType.VarChar);
                MySqlParameter parDimension = new MySqlParameter("Dimension", MySqlDbType.VarChar);
                MySqlParameter parPricePerUnit = new MySqlParameter("PricePerUnit", MySqlDbType.Float);
                MySqlParameter parUnitName = new MySqlParameter("UnitName", MySqlDbType.VarChar);

                insertCmd.Parameters.Add(parRequisitionID);
                insertCmd.Parameters.Add(parQuantity);
                insertCmd.Parameters.Add(parClassifier);
                insertCmd.Parameters.Add(parWeight);
                insertCmd.Parameters.Add(parProductName);
                insertCmd.Parameters.Add(parDimension);
                insertCmd.Parameters.Add(parPricePerUnit);
                insertCmd.Parameters.Add(parUnitName);

                updateCmd.Parameters.Add(parQuantity);
                updateCmd.Parameters.Add(parProductName);
                updateCmd.Parameters.Add(parDimension);

                insertCmd.CommandText = "INSERT INTO requisitioniddescription (RequisitionID, Quantity, Classifier, Weight, ProductName, " +
                    "Dimension, PricePerUnit, UnitName) " +
                    "VALUES (?RequisitionID, ?Quantity, ?Classifier, ?Weight, ?ProductName, ?Dimension, ?PricePerUnit, ?UnitName)";

                updateCmd.CommandText = "UPDATE product SET ItemsRemain = itemsRemain - ?Quantity " +
                    "WHERE Name = ?ProductName AND Dimension = ?Dimension";

                getRequisitionIDCmd.CommandText = "SELECT @@IDENTITY AS 'LastID'";
                MySqlDataReader dbReader = getRequisitionIDCmd.ExecuteReader(); 
                dbReader.Read(); 
                lastRequisitionID = Int32.Parse(dbReader.GetValue(0).ToString()); 
                dbReader.Dispose();

                for (int i = 0; i < AddRequisitionFormDataGridView.RowCount; i++)
                {
                    parRequisitionID.Value = lastRequisitionID;
                    parQuantity.Value = AddRequisitionFormDataGridView[0, i].Value.ToString();
                    parClassifier.Value = AddRequisitionFormDataGridView[1, i].Value.ToString();

                    if (AddRequisitionFormDataGridView[2, i].Value.ToString() == "")
                        parWeight.Value = null;
                    else
                        parWeight.Value = AddRequisitionFormDataGridView[2, i].Value.ToString();

                    parProductName.Value = AddRequisitionFormDataGridView[3, i].Value.ToString();
                    parDimension.Value = AddRequisitionFormDataGridView[4, i].Value.ToString();
                    parPricePerUnit.Value = AddRequisitionFormDataGridView[5, i].Value.ToString();
                    //parUnitName.Value = AddRequisitionFormDataGridView[7, i].Value.ToString();
                    parUnitName.Value = AddRequisitionFormDataGridView[6, i].Value.ToString();

                    insertCmd.ExecuteNonQuery();
                    updateCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally 
            {
                _connection.Close();
                ShowCompleteMessage(lastRequisitionID);
            }
        }

        private void OpenReport(int reportType, int reportID)
        {
            ShowPriceForm showPriceForm = new ShowPriceForm();
            ShowReportForm showReportForm;

            switch (showPriceForm.ShowDialog())
            {
                case DialogResult.OK:
                    showReportForm = new ShowReportForm(_connectionString);
                    showReportForm.setReportToShow(reportType, reportID, 
                        !showPriceForm.ShowPriceCheckBox.Checked, !showPriceForm.ShowWeightCheckBox.Checked, null);
                    showReportForm.LoadReport();
                    showReportForm.ShowDialog();
                    break;
                default:
                    break;
            }
        }

        private void ShowCompleteMessage(int reportID)
        {
            AddCompleteForm addCompleteForm = new AddCompleteForm();
            switch (addCompleteForm.ShowDialog())
            {
                case DialogResult.OK:
                    FormChangedEvent("REQUISITION_SELECTION", null);
                    OpenReport(2, reportID);
                    break;
                default:
                    FormChangedEvent("REQUISITION_SELECTION", null);
                    break;
            }
        }

        private void CustomerNameComboBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            AddRequisitionFormButton.Visible = (_dataGridViewDataTable.Rows.Count != 0 && CustomerNameComboBox.SelectedIndex != -1);
        }

        private void DeliveryCheckBox_CheckedChanged(object sender, EventArgs e)
        {
            DeliveryNumericUpDown.Visible = DeliveryCheckBox.Checked;
            UnitLabel.Visible = DeliveryCheckBox.Checked;
        }

        private void IncludeVatCheckBox_CheckedChanged(object sender, EventArgs e)
        {
            CalculateTotal();
        }

        private void IncludeVatUpDown_ValueChanged(object sender, EventArgs e)
        {
            var s = sender as System.Windows.Forms.NumericUpDown;
            if (s != null)
            {
                Utility.SaveDefaultVat((int)s.Value);
                CalculateTotal();
            }
        }
    }
}